using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2012，云里科技
    /// 内容摘要: EDMSQLITESmtp
    /// 完成日期：2012年8月14日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSQLITESmtp
    {
        /// <summary>
        /// 检测Smtps表是否存在，如果不存在就创建
        /// </summary>
        /// <returns></returns>
        public static bool CheckSmtpsIsExist()
        {
            bool bValue = false;
            try
            {
                string strNum = "";
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(*) as num FROM sqlite_master where type='table' and name='Smtps'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    strNum = dr["num"].ToString();
                    dr.Dispose();
                    cn.Close();

                    if (strNum == "0")
                    {
                        CreateTableForSmtps();
                        bValue = false;
                    }
                    else
                    {
                        bValue = true;
                    }
                }
                else
                {
                    dr.Dispose();
                    cn.Close();
                }

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测Smtps表是否存在 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 创建Smtps表
        /// </summary>
        public static void CreateTableForSmtps()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                //autoincrement
                string strSql = "create table Smtps(smtp_id int primary key,email_address nvarchar(50),smtp_server nvarchar(50),email_username nvarchar(50),email_password nvarchar(50),smtp_credential nvarchar(50),is_ssl int,is_ok int,user_id int,post_status char(1),email_type nvarchar(10),send_status char(1),server_smtp_id int)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("创建Smtps表 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 新增SMTP服务器
        /// </summary>
        /// <param name="strAddress">电子邮件地址</param>
        /// <param name="strServer">SMTP服务器</param>
        /// <param name="strUserName">用户名</param>
        /// <param name="strPassword">密码</param>
        public static void InsertSMTP(string strAddress,string strServer,string strUserName,string strPassword,string strServerSMTPID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into Smtps(smtp_id,email_address,smtp_server,email_username,email_password,server_smtp_id) " +
                    " values(" + GetAutoAddNum() + ",'" + strAddress + "','" + strServer + "','" + strUserName + "','" + strPassword + "'," + strServerSMTPID + ")";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("添加SMTP -> " + ex.Message);
            }
        }

        /// <summary>
        /// Emails获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select smtp_id from Smtps order by smtp_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["smtp_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("SMTP获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 更新SMTP状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        public static void UpdateSMTPStatus(string strStatus,int iSmtpID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Smtps set post_status=" + strStatus + " where smtp_id=" + iSmtpID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();

                if (CountAllSMTPStatus() == 1)//当用户只添加了一条数据时
                {
                    SEO.DAL.EDMSQLITESmtp.UpdateAllSMTPStatus("0");
                }

                if (GetCountSMTPAmount() == 1)//当用户添加了多条，循环发送只剩下最后一条时
                {
                    SEO.DAL.EDMSQLITESmtp.UpdateAllSMTPStatus("0");
                }
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新SMTP状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新所有SMTP服务器数据信息状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        public static void UpdateAllSMTPStatus(string strStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Smtps set post_status=" + strStatus;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新SMTP状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取未发布SMTP信息数量
        /// </summary>
        /// <returns></returns>
        public static int GetCountSMTPAmount()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(smtp_id) AS cnum FROM Smtps WHERE (post_status = 0)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["cnum"].ToString());
                }
                dr.Dispose();
                cn.Close();
                
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取未发布SMTP信息数量 -> " + ex.Message);
            }

            return iNum;
        }

        

        /// <summary>
        /// 删除SMTP
        /// </summary>
        public static void DeleteAllSMTP()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from Smtps";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除SMTP -> " + ex.Message);
            }
        }

        /// <summary>
        /// 统计共有多少ＳＭＴＰ数据
        /// </summary>
        /// <returns></returns>
        public static int CountAllSMTPStatus()
        {
            int iCount = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select count(smtp_id) as num from Smtps";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iCount = int.Parse(dr["num"].ToString());
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新SMTP状态 -> " + ex.Message);
            }

            return iCount;
        }



    }
}
